package com.company.project.robot.service.impl;

import com.alibaba.fastjson.JSON;
import com.company.project.common.exception.BusinessException;
import com.company.project.common.utils.ExcelUtils;
import com.company.project.robot.entity.LanternRiddlesEntity;
import com.company.project.robot.mapper.LanternRiddlesMapper;
import com.company.project.robot.service.LanternRiddlesService;
import com.company.project.robot.vo.ReptilesVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Connection;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.select.Elements;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;



@Service("lanternRiddlesService")
@Slf4j
public class LanternRiddlesServiceImpl extends ServiceImpl<LanternRiddlesMapper, LanternRiddlesEntity> implements LanternRiddlesService {


    /**
     * 导出模板
     * @param request
     * @param response
     * @throws IOException
     */
    @Override
    public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String[] excelHeader = { "谜语","谜底","含义" };
        String[] columnNames = { "riddle","answer","remake" };
        int[] ds_format = { 2, 2, 2 };
        String[] sumColumns = { "", "", ""};
        ExcelUtils.export("谜语导入模板.xlsx", null, excelHeader, columnNames, sumColumns, ds_format, null,
                null, request, response);
    }

    /**
     * 导入
     * @param file
     * @return
     * @throws Exception
     */
    @Override
    public String excelImport(MultipartFile file) throws Exception {
        //获取Excel格式
        int type = readExcel(file.getOriginalFilename());
        Workbook wb = null;
        //获取第一个工作表的数据（其他工作表数据不操作）
        Sheet sheet = null;
        //xls格式
        if(type == 1) {
            wb = new HSSFWorkbook(file.getInputStream());
        }else if(type == 2) {//xlsx格式
            wb = new XSSFWorkbook(file.getInputStream());
        }else {
            throw new BusinessException("请导入正确文件类型");
        }
        if(wb != null && wb.getNumberOfSheets() > 0) {
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                if(wb.getSheetAt(i).getPhysicalNumberOfRows() > 0) {
                    sheet = wb.getSheetAt(i);
                    break;
                }
            }
        } else {
            throw new BusinessException("导入失败");
        }
        //判断第一行的第1、2列的名称，不符合则提示模板文件不对
        Row r = sheet.getRow(0);
        if(!r.getCell(0).getStringCellValue().equals("谜语")
                || !r.getCell(1).getStringCellValue().equals("谜底") ||
                !r.getCell(2).getStringCellValue().equals("含义")) {
            throw new BusinessException("请选择正确的模板文件再导入");
        }
        List<LanternRiddlesEntity> list =new ArrayList<>();
        for (int i = 1, length = sheet.getLastRowNum(); i <= length; i++){
            Row row = sheet.getRow(i);
            //判断第1，2列是否有空值
            if(
               row.getCell(0) == null ||
               row.getCell(1) == null
            ) {
                throw new BusinessException( "部分数据导入成功<br>,已导入到第"+i+"行,在【"+(i+1)+"】行有空数据");
            }
            LanternRiddlesEntity entity =new LanternRiddlesEntity();
            entity.setRiddle(row.getCell(0).getStringCellValue());
            entity.setAnswer(row.getCell(1).getStringCellValue().replace(" ", ""));
            if(null !=row.getCell(2)  ){
                entity.setRemake(row.getCell(2).getStringCellValue());
            }

            entity.setDeleted(1);
            list.add(entity);
        }
        this.saveBatch(list);
        return "导入成功";
    }

    /**
     * 读取excel格式
     * @param fileName
     * @return
     * @throws Exception
     */
    private int readExcel(String fileName) {
        String extString = fileName.substring(fileName.lastIndexOf("."));
        try {
            if (".xls".equals(extString)) {
                return 1;
            }
            else if(".xlsx".equals(extString)) {
                return 2;
            }
            else {
                return 0;
            }
        } catch (Exception e) {
            log.error("读取Excel文件异常"+e);
            throw new BusinessException("读取Excel文件异常");
        }
    }

    /**
     * 异步爬虫爬取汉迷网数据
     * @param reptilesVo
     * @return
     * @throws IOException
     */
    @Async
    @Override
    public void listSave(ReptilesVo reptilesVo) throws IOException {
        List<LanternRiddlesEntity> list = new ArrayList<>();
        for (int i = reptilesVo.getStart(); i <= reptilesVo.getEnd(); i++) {
            LanternRiddlesEntity entity = new LanternRiddlesEntity();
            Connection connection_context = Jsoup
                    .connect("http://www.cmiyu.com/dmmy/" + i + ".html")
                    .userAgent("Mozilla/4.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)")
                    .timeout(3000);
            try {
                if (connection_context.execute().statusCode() == 200) {
                    Document document_context = connection_context.get();
                    Elements text = document_context.select(".content")
                            .select(".md");
                    Elements text1 = document_context.select(".content")
                            .select(".zy");
                    entity.setRiddle(text.select("h3").get(0).text().replaceFirst("谜面：", ""));
                    entity.setAnswer(text.select("h3").get(1).text().replaceFirst("谜底：", ""));
                    entity.setRemake(text1.text());
                    entity.setDeleted(1);
                    log.info("地址" + i + ": OK ");
                } else {
                    log.info("地址" + i + ": 不可用");
                }
            }catch (Exception ex){
                log.error("地址" + i + ": 不可用");
            }
            if(null !=  entity.getRiddle()){
                list.add(entity);
            }
        }
        this.remove(null);
        this.saveBatch(list);
    }


}